package model;
import lib.classes.Profile;
import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.DatabaseSecurityOptions;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
import net.rim.device.api.system.DeviceInfo;
public class DB 
{
	private final String DBFILE = "minglevd.db";
	private final String DBLOCATION = "file:///SDCard/databases/mingleVD/";
	public static final String serverURL = "http://voiddigits.com/apps/mingle/mingleserver.php";
	private URI myURI;
	private Database db;
	public DB()
	{
		createDB();
	}
	
	private void createDB()
	{
		try
		{		
		   myURI = URI.create( DBLOCATION + DBFILE );
		   if(!DatabaseFactory.exists(myURI))
		   {
			   db = DatabaseFactory.openOrCreate(myURI,new DatabaseSecurityOptions(false));
			   //closeDB();
			   createProfileTable();
			   createSettingsTable();	
			   createLoginTable();
			   closeDB();
		   }
		}
		catch ( Exception e )
		{
		   System.out.println( e.getMessage() );
		   e.printStackTrace();
		}
	}
	private void openDB()
	{
		try{
		db = DatabaseFactory.openOrCreate(myURI);
		System.out.println("DB IS OPEN");
		}catch(Exception e)
		{
			System.out.println("Error Opening DB:: " +e.toString());
		}
	}
	private void closeDB() throws Exception
	{
		if(db != null)
		{
			db.close();
			System.out.println("DB IS CLOSED");
		}
	}
	
	private void createLocationTable()
	{
		String tblLocation = "CREATE TABLE Location(locationID INTEGER PRIMARY KEY," +
				"country TEXT, province TEXT, city TEXT)";
		try{
			//openDB();
		   Statement st = db.createStatement( tblLocation );
		   st.prepare();
		   st.execute();
		   st.close();
		   //closeDB();
		}catch(Exception e)
		   {
			   
		   }
	}
	private void createSettingsTable()
	{
		String tblSettings = "CREATE TABLE Settings(";
		   tblSettings += "browseGender TEXT, ";
		   tblSettings += "browseAge TEXT, ";
		   tblSettings += "browseCity TEXT, ";
		   tblSettings += "browseProvince TEXT, ";
		   tblSettings += "browseCountry TEXT, ";
		   tblSettings += "browseScreenName TEXT ";
		   tblSettings += ")";
		   try{
			   //openDB();
			   Statement st = db.createStatement( tblSettings );
			   st.prepare();
			   st.execute();
			   st.close();
			   String sql = "INSERT INTO SETTINGS (browseGender) VALUES('')";
	            st = db.createStatement(sql);
				st.prepare();
	            st.execute();
	            st.close();
			   //closeDB();
		   }catch(Exception e)
		   {
			   System.out.println("Error creating settings table::: "+e.getMessage());
		   }
	}
	
	private void createProfileTable()
	{
		String tblProfile = "CREATE TABLE IF NOT EXISTS Profile(id INTEGER primary key, " +
				"pin TEXT, screenname TEXT, dob TEXT, status TEXT, aboutMe TEXT, " +
				"gender TEXT, searchable TEXT , picture BLOB)";
		try{
			//openDB();
			Statement st = db.createStatement(tblProfile);
			st.prepare();
            st.execute();
            st.close();
            /*String sql = "INSERT INTO 'PROFILE' ('pin', picture) VALUES('"+DeviceInfo.getDeviceId()+"', ZEROBLOB( 1024 ))";
            st = db.createStatement(sql);
			st.prepare();
            st.execute();
            st.close();*/
            //closeDB();
		}catch(Exception e)
		{
			System.out.println("Error creating Profile table::: "+e.getMessage());
		}
	}
	
	public void createLoginTable()
	{
		String tblLogin = "CREATE TABLE IF NOT EXISTS Login(username TEXT, " +
		"password TEXT, remember BOOLEAN)";
		try{
			
			Statement st = db.createStatement(tblLogin);
			st.prepare();
            st.execute();
            st.close();
            
		}catch(Exception e)
		{
			System.out.println("Error creating Profile table::: "+e.getMessage());
		}
	}
	
	public void insert(String sql)
	{
		try{
			openDB();
			Statement st = db.createStatement(sql);// Statement();
			st.prepare();
            st.execute();
            st.close();
            closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public void insertProfileIntoDB(Profile p)
	{
		try{
			openDB();
			
			Statement st = db.createStatement("DELETE from Profile");
			st.prepare();
			st.execute();
			st.close();
			
			String sql = "UPDATE PROFILE SET 'screenname'='"+p.getScreenname()+"', " +
					"'dob'='"+p.getDOB().getTime()+"', 'status'='"+p.getStatus()+"', 'aboutMe' ='"+p.getAboutme()+"'," +
					"'gender'='"+p.getGender()+"', 'searchable' ='"+(p.isPrivate()? "false": "true")+"'"+
					 " WHERE 'pin'='"+DeviceInfo.getDeviceId()+"'";
			sql = "INSERT INTO PROFILE(pin,screenname,dob,status,aboutMe,gender,searchable) " +
					"VALUES('"+DeviceInfo.getDeviceId()+"','"+p.getScreenname()+"','"+p.getDOB().getTime()+"'," +
							"'"+p.getStatus()+"','"+p.getAboutme()+"','"+p.getGender()+"', '"+(p.isPrivate()? "false": "true")+"')";
			
			sql = "INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)";
			//db.executeStatement(sql+";");
			byte[] b = p.getImage().getData();
			
			st = db.createStatement("INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)");
			st.prepare();
			st.bind(1, DeviceInfo.getDeviceId());
			st.bind(2, p.getScreenname());
			st.bind(3, p.getDOB().getTime());
			st.bind(4, p.getStatus());
			st.bind(5, p.getAboutme());
			st.bind(6, p.getGender());
			st.bind(7, (p.isPrivate()? "false": "true"));
			st.bind(8, b);
			//st.bind(1, p.getImage().getData());
            st.execute();
            st.close();
            
            
            
            /*try {
            	System.out.println("Doing the BLOB DATA NOW");
            	db.beginTransaction();
                byte[] b = p.getImage().getData();
                st = db.createStatement("UPDATE PROFILE SET picture=?");
                st.bind(1, b);
                st.execute();
                db.commitTransaction();
                //con.commit();
            }
            catch (Exception e) {
                //handle exception (consider con.rollback()) and con maybe null here)
            	db.rollbackTransaction();
            	System.out.println("ERROR in UPDATE DB BLOB:: " + e.getMessage() + " :: " + e.toString());
            }
            finally {
                st.close();
            }
            */
            /*java.io.OutputStream outputStream = db.createBlobOutputStream( "PROFILE", "picture", db.lastInsertedRowID( ) );
            byte[] data = p.getImage().getData();
            for( int i = 0; i < data.length; i++ ) 
            {
                outputStream.write( data[i] );
            }
            outputStream.close( );*/
			closeDB();
		}catch(Exception e)
		{
			System.out.println("ERROR in UPDATE DB:: " + e.getMessage() + " :: " + e.toString());
		}
	}
	
	public void updateDB(Profile p)
	{
		try{
			openDB();
			
			String sql = "UPDATE PROFILE SET 'screenname'='"+p.getScreenname()+"', " +
					"'dob'='"+p.getDOB().getTime()+"', 'status'='"+p.getStatus()+"', 'aboutMe' ='"+p.getAboutme()+"'," +
					"'gender'='"+p.getGender()+"', 'searchable' ='"+(p.isPrivate()? "false": "true")+"'"+
					 " WHERE 'pin'='"+DeviceInfo.getDeviceId()+"'";
			sql = "INSERT INTO PROFILE(pin,screenname,dob,status,aboutMe,gender,searchable) " +
					"VALUES('"+DeviceInfo.getDeviceId()+"','"+p.getScreenname()+"','"+p.getDOB().getTime()+"'," +
							"'"+p.getStatus()+"','"+p.getAboutme()+"','"+p.getGender()+"', '"+(p.isPrivate()? "false": "true")+"')";
			
			sql = "INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)";
			//db.executeStatement(sql+";");
			byte[] b = p.getImage().getData();
			
			Statement st = db.createStatement("INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)");
			st.prepare();
			st.bind(1, DeviceInfo.getDeviceId());
			st.bind(2, p.getScreenname());
			st.bind(3, p.getDOB().getTime());
			st.bind(4, p.getStatus());
			st.bind(5, p.getAboutme());
			st.bind(6, p.getGender());
			st.bind(7, (p.isPrivate()? "false": "true"));
			st.bind(8, b);
			//st.bind(1, p.getImage().getData());
            st.execute();
            
            st.close();
            
            
            
            /*try {
            	System.out.println("Doing the BLOB DATA NOW");
            	db.beginTransaction();
                byte[] b = p.getImage().getData();
                st = db.createStatement("UPDATE PROFILE SET picture=?");
                st.bind(1, b);
                st.execute();
                db.commitTransaction();
                //con.commit();
            }
            catch (Exception e) {
                //handle exception (consider con.rollback()) and con maybe null here)
            	db.rollbackTransaction();
            	System.out.println("ERROR in UPDATE DB BLOB:: " + e.getMessage() + " :: " + e.toString());
            }
            finally {
                st.close();
            }
            */
            /*java.io.OutputStream outputStream = db.createBlobOutputStream( "PROFILE", "picture", db.lastInsertedRowID( ) );
            byte[] data = p.getImage().getData();
            for( int i = 0; i < data.length; i++ ) 
            {
                outputStream.write( data[i] );
            }
            outputStream.close( );*/
			closeDB();
		}catch(Exception e)
		{
			System.out.println("ERROR in UPDATE DB:: " + e.getMessage() + " :: " + e.toString());
		}
	}

	public void login(String username, String pass, boolean remember) 
	{
		try{
			openDB();
				Statement st = db.createStatement("DELETE FROM Login");
	            st.prepare();
	            st.execute();
	            
	            st = db.createStatement("INSERT INTO Login VALUES(?, ?, ?)");
	            st.prepare();
				st.bind(1, username);
				st.bind(2, pass);
				st.bind(3, remember);
				st.execute();
			closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public boolean rememberLogin() 
	{
		try{
			openDB();
				Statement st = db.createStatement("SELECT username, password, remember FROM Login Where remember=true");
	            st.prepare();
	            st.execute();
	            
	            Cursor c = st.getCursor();
	            if(!c.isEmpty())
	            {
	            	c.first();
		            Row r = c.getRow();
		            return (boolean)r.getBoolean(2);
	            }
				st.execute();
			closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
}
